﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LPY.LeHealth.DTO;
using System.Data;
using System.Data.SqlClient;
using LPY.LeHealth.Model;

namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;

    public class AppDAL : BaseDAL
    {
        /// <summary>
        /// 显示评论表信息
        /// </summary>
        /// <returns></returns>
        public List<AppDTO> show()
        {
            using (var cnn = DbConnection)
            {
                //var sql = cnn.From<HospitalDTO>()
                //  .LeftJoin<AreaDTO>()
                //  .LeftJoin<GradeDTO>();
                var sql = cnn.From<AppDTO>()
                    .LeftJoin<UserDTO>((a, u) => u.Id == a.uid)
                    .LeftJoin<Apptype>((a, t) => a.Aid == t.Id)
                    .Select<AppDTO, UserDTO, Apptype>((a, u, t) => new
                {
                    a,
                    u,
                    t,
                    a.Id,
                    a.Aid,
                    a.pcontent,
                    a.pcount,
                    a.pdate,
                    a.pzhuangtai,
                    a.uid,
                    u.Gender,
                    u.ImgUrl,
                    u.Name,
                    u.Password,
                    u.PhoneNumber,
                    u.PrivileteId,
                    u.RoleId,
                    u.TrueName,
                    t.Aname,
                }).Take(3).OrderByDescending(m=>m.Id);
                return cnn.Select<AppDTO>(sql);
            }
            //SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=szy1021;Integrated Security=True");
            //con.Open();
            //string sql = "select  top 5 t.*,t_userdto.Name from(select t_appdto.*,t_apptype.Aname from t_appdto left join t_apptype on  t_appdto.uid=t_apptype.id)t left join t_userdto on t_userdto.id=t.uid";
            //SqlCommand com = new SqlCommand(sql,con);
            //SqlDataAdapter ad = new SqlDataAdapter(com);
            //DataTable dt = new DataTable();
            //ad.Fill(dt);
            //List<AppModel> list = new List<AppModel>();

            //foreach (DataRow i in dt.Rows)
            //{
            //    AppModel app = new AppModel();
               
            //    app.uid = Convert.ToInt32(i["uid"]);
            //    app.pdate = Convert.ToDateTime(i["pdate"]).ToString("yyyy/MM/dd");
            //    app.pcount = Convert.ToInt32(i["pcount"]);
            //    app.pcontent = i["pcontent"].ToString();
            //    app.Aid = Convert.ToInt32(i["Aid"]);
            //    app.Aname = i["Aname"].ToString();
            //    app.Name = i["Name"].ToString();
            //    list.Add(app);
            //}

            //return list;
           

        }

        /// <summary>
        /// 显示全部评论表信息
        /// </summary>
        /// <returns></returns>
        public List<AppModel> showall()
        {

            SqlConnection con = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True");
            con.Open();
            string sql = "select  t.*,t_userdto.Name from(select t_appdto.*,t_apptype.Aname from t_appdto left join t_apptype on  t_appdto.uid=t_apptype.id)t left join t_userdto on t_userdto.id=t.uid";
            SqlCommand com = new SqlCommand(sql, con);
            SqlDataAdapter ad = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            List<AppModel> list = new List<AppModel>();

            foreach (DataRow i in dt.Rows)
            {
                AppModel app = new AppModel();
               
                app.uid = Convert.ToInt32(i["uid"]);
                app.pdate = Convert.ToDateTime(i["pdate"]).ToString("yyyy/MM/dd");
                app.pcount = Convert.ToInt32(i["pcount"]);
                app.pcontent = i["pcontent"].ToString();
                app.Aid = Convert.ToInt32(i["Aid"]);
                app.Aname = i["Aname"].ToString();
                app.Name = i["Name"].ToString();
                list.Add(app);
            }

            return list;
        }
        public List<AppModel> plshow(int uid)//记录登录用户的ID 
        {
            SqlConnection con = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True");
            con.Open();
            string sql = "select  t.*,t_userdto.Name from(select t_appdto.*,t_apptype.Aname from t_appdto left join t_apptype on  t_appdto.uid=t_apptype.id)t left join t_userdto on t_userdto.id=t.uid where uid='"+uid+"' and t.pzhuangtai=1";
            SqlCommand com = new SqlCommand(sql, con);
            SqlDataAdapter ad = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            List<AppModel> list = new List<AppModel>();

            foreach (DataRow i in dt.Rows)
            { 
                AppModel app = new AppModel();
                
                app.uid = Convert.ToInt32(i["uid"]);
                app.pdate = Convert.ToDateTime(i["pdate"]).ToString("yyyy/MM/dd");          
                app.pcontent = i["pcontent"].ToString();
                app.Aid = Convert.ToInt32(i["Aid"]);
                app.Aname = i["Aname"].ToString();
                app.Name = i["Name"].ToString();
                list.Add(app);
            }

            return list;
        }
        public List<AppModel> plshowwei(int uid)//记录登录用户的ID 
        {
            SqlConnection con = new SqlConnection("Data Source=DESKTOP-4O5PKGG\\SQLEXPRESS;Initial Catalog=szy1021;Integrated Security=True");
            con.Open();
            string sql = "select  t.*,t_userdto.Name from(select t_appdto.*,t_apptype.Aname from t_appdto left join t_apptype on  t_appdto.uid=t_apptype.id)t left join t_userdto on t_userdto.id=t.uid where uid='"+uid+"' and t.pzhuangtai=0";
            SqlCommand com = new SqlCommand(sql, con);
            SqlDataAdapter ad = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            List<AppModel> list = new List<AppModel>();

            foreach (DataRow i in dt.Rows)
            {
                AppModel app = new AppModel();
                
                app.uid = Convert.ToInt32(i["uid"]);
                app.pdate = Convert.ToDateTime(i["pdate"]).ToString("yyyy/MM/dd");
                app.pcontent = i["pcontent"].ToString();
                app.Aid = Convert.ToInt32(i["Aid"]);
                app.Aname = i["Aname"].ToString();
                app.Name = i["Name"].ToString();
                list.Add(app);
            }

            return list;
        }

        public List<imageModel> getimage()//记录登录用户的ID 
        {
            //SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=szy1021;Integrated Security=True");
            //con.Open();
            //string sql = "select * from t_image";
            //SqlCommand com = new SqlCommand(sql, con);
            //SqlDataAdapter ad = new SqlDataAdapter(com);
            //DataTable dt = new DataTable();
            //ad.Fill(dt);
            //List<imageModel> list = new List<imageModel>();

            //foreach (DataRow i in dt.Rows)
            //{
            //    imageModel image = new imageModel();

            //    image.iurl = i["iurl"].ToString();
            //    image.itourl = i["itourl"].ToString();
            //    list.Add(image);
               
            //}

            //return list;
            using (var cnn=DbConnection)
            {
                var sql = cnn.From<imageModel>();
                return cnn.Select(sql);
            }
        }
    }
}
